#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
CREATE TABLE hive.$1zx_dws.dws_leave AS
WITH tmp as (
SELECT
    class_date,
    q.class_id,
    student_id,

    DATE_FORMAT(CAST(begin_time AS TIMESTAMP), '%Y-%m-%d') as begin_date,
    DATE_FORMAT(CAST(begin_time AS TIMESTAMP), '%H:%i:%s') as begin_time ,
    DATE_FORMAT(CAST(end_time AS TIMESTAMP), '%Y-%m-%d') as end_date,
    DATE_FORMAT(CAST(end_time AS TIMESTAMP), '%H:%i:%s') as end_time,

    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    leave_remark

FROM hive.$1zx_dwd.fact_student_leave_apply q
LEFT JOIN hive.$1zx_dwd.dim_course_table_upload_detail ctu ON q.class_id = ctu.class_id
LEFT JOIN hive.$1zx_dwd.dim_tbh_class_time_table tct ON q.class_id = tct.class_id
WHERE q.audit_state = 1 AND q.cancel_state = 0  AND (content IS NOT NULL )
AND (begin_time BETWEEN use_begin_date AND use_end_date) AND (end_time BETWEEN use_begin_date AND use_end_date)
)
SELECT
    class_date,
    class_id,
    student_id,
    if(class_date BETWEEN begin_date AND end_date,1,0) as a,   -- 判断请假几天
    if(begin_time < morning_begin_time and end_time > morning_end_time,1,0) as type_m,  --判断早上是否请假
    if(begin_time < afternoon_begin_time and end_time > afternoon_end_time,1,0) as type_a,
    if(begin_time < evening_begin_time and end_time > evening_end_time,1,0) as type_e
FROM tmp;
CREATE TABLE hive.$1zx_dws.dws_signin as
WITH tmp as (
SELECT
    class_id,
    signin_date,
    morning,
    afternoon,
    evening,
    type_m,
    type_a,
    type_e,
    SUM(if(signin_time = min_signin,1,0)) as total_amount

FROM hive.$1zx_dwb.dwb_signin
GROUP  BY GROUPING SETS
(signin_date),
(signin_date,class_id),
(signin_date,class_id,morning,type_m),
(signin_date,class_id,afternoon,type_a),
(signin_date,class_id,evening,type_e)
)
SELECT
    tmp.class_id,
    signin_date,
    CASE WHEN type_m = 1 THEN 'morning'
    WHEN morning = 1 AND type_m = 0 THEN 'morning_last'
    WHEN type_a = 1 THEN 'afternoon'
    WHEN type_a = 0 AND afternoon = 1 THEN 'afternoon_last'
    WHEN type_e = 1 THEN 'evening'
    WHEN type_e = 0 AND evening = 1 THEN 'evening_last'
    END time_type,
    total_amount
FROM tmp;
"